﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class UnitProvide
    {
        Database db;
        public UnitProvide()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        #region <<  Unit  >>

        public DataSet getUintByGovern(string GovernID)
        {
            return getUintByGovern("",GovernID, false);
        }

        public DataSet getUnitByGovern(string UnitName, string GovernID)
        {
            return getUintByGovern(UnitName, GovernID, false);
        }

        public DataSet getUintByGovern(string UnitName,string GovernID,bool IsAll)
        {
            string strSQL = " SELECT * FROM [Unit] WHERE  1= 1 ";
            if (GovernID.Trim().Length > 0)
            {
                strSQL += "  AND GovernID = '" + GovernID + "' ";
            }
            if (UnitName.Trim().Length > 0)
            {
                strSQL += "  AND UnitName like '%" + UnitName + "%' ";
            }

            if (!IsAll)
            {
                strSQL += " AND status =  'A' ";
            }
            strSQL += "    ORDER BY UnitID ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int DeleteUnitByID(string ID)
        {
            string strSQL = "DELETE FROM [Unit] WHERE [ID] = " + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int InsertUnit(string UnitID, string GovernID, string UnitName, string address, string PostalCode, string ContactPerson, string ContactPhone, string HospitalLevel,string MaxID,string status)
        {
            string strSQL = "INSERT INTO [Unit] (UnitID,GovernID,UnitName,address,PostalCode,ContactPerson,ContactPhone,HospitalLevel,MaxID,status)VALUES('"+UnitID+"','"+GovernID+"','"+UnitName+"', '"+address+"','"+PostalCode+"','"+ContactPerson+"','"+ContactPhone+"','"+ HospitalLevel+"','"+MaxID+"','"+status+"') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateUnit(string ID,string UnitID, string GovernID, string UnitName, string address, string PostalCode, string ContactPerson, string ContactPhone, string HospitalLevel, string MaxID, string status)
        {
            string strSQL = "UPDATE [Unit] SET  UnitID='" + UnitID + "', GovernID = '" + GovernID + "', UnitName = '" + UnitName + "', address='" + address + "', PostalCode='" + PostalCode + "', ContactPerson='" + ContactPerson + "', ContactPhone='" + ContactPhone + "',HospitalLevel='" + HospitalLevel + "', MaxID='" + MaxID + "', status = '" + status + "' WHERE ID = " + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getUnitByID(string ID)
        {
            string strSQL = "SELECT * FROM [Unit] WHERE ID = "+ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getUnitNameByID(string UnitID)
        {
            string strSQL = "SELECT UnitName FROM unit WHERE UnitID = '" + UnitID+"'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }
        #endregion

        #region <<  Govern  >>

        public DataSet getGovern()
        {
            string strSQL = " SELECT * FROM [Govern] WHERE status = 'A' ORDER BY GovernID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int UpdateAllowApplyofGovern(string GovernID,string allowApply)
        {
            string strSQL = "UPDATE Govern SET ApplyConfirmedInd= '" + allowApply + "' where 1=1 ";
            if (!string.IsNullOrEmpty(GovernID))
            {
                strSQL += " AND GovernID ='" + GovernID + "'";
            }
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getGovern(string GovernName, string ApplyStatus)
        {
            string strSQL = "SELECT * FROM [Govern] WHERE 1=1 ";
            if (GovernName.Trim().Length > 0)
            {
                strSQL += " AND GovernName like '%" + GovernName.Trim() +"%'  ";
            }
            if (ApplyStatus.Trim().Length > 0)
            {
                strSQL += " AND ApplyConfirmedInd = '" + ApplyStatus + "' ";
            }

            strSQL += " ORDER BY GovernID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int InsertGovern(string GovernID, string GovernName, string PostalCode, string ContactPerson, string ContactPhone, string ApplyConfirmedInd, string Status)
        {
            string strSQL = "INSERT INTO Govern(GovernID,GovernName,PostalCode,ContactPerson,ContactPhone,ApplyConfirmedInd,Status)VALUES('" + GovernID + "','" + GovernName + "','" + PostalCode + "', '" + ContactPerson + "', '" + ContactPhone + "', '" + ApplyConfirmedInd + "', '" + Status + "')";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateGovern(string ID, string GovernID, string GovernName, string PostalCode, string ContactPerson, string ContactPhone, string ApplyConfirmedInd, string Status)
        {
            string strSQL = "UPDATE Govern SET GovernID='" + GovernID + "', GovernName='" + GovernName + "', PostalCode='" + PostalCode + "' , ContactPerson='" + ContactPerson + "' , ContactPhone='" + ContactPhone + "' , ApplyConfirmedInd='" + ApplyConfirmedInd + "',Status='" + Status + "' WHERE ID= " + ID ;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet getGovernByID(string ID)
        {
            string strSQL = "SELECT * FROM Govern WHERE ID=" + ID ;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getGovernByGovernID(string GovernID)
        {
            string strSQL = "SELECT * FROM Govern WHERE 1=1 ";
            if(GovernID.Length>0)
            {
                strSQL += " AND GovernID='" + GovernID+"' ";
            }
            strSQL += "  AND Status = 'A' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int DeleteGovernByID(string ID)
        {
            string strSQL = "DELETE FROM [Govern] WHERE [ID] = "+ID ;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        public string GetGovernNameByID(string GovernID)
        {
            string strSQL = "SELECT GovernName FROM Govern WHERE GovernID ='" + GovernID+"' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }
        #endregion
    }
}
